/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Business;

import Connections.Database;
import Entities.EntReportExhibitorFinancialList;
import Entities.EntReportExhibitorFinancialListFull;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;
//import javax.swing.text.html.parser.Entity;

/**
 *
 * @author Nguyen Hoang Viet <nguyenhoangvietitt@gmail.com>
 */
public class BssGetDataReportExhibitorFinancialList {

    Connection conn = null;
    ResultSet rs;

    public BssGetDataReportExhibitorFinancialList() {
        //conn = new connection();
        Database dCnn = new Database();
        conn = dCnn.getConn();
    }

//    public ResultSet executeQuery(String name) throws SQLException {
//        if (conn != null) {
//            CallableStatement clst = conn.prepareCall("{call " + name + "}");
//            return clst.executeQuery();
//        }
//        return null;
//    }
//    public ResultSet executeQuery(String name, int Id) throws SQLException {
//        if (conn != null) {
//            CallableStatement clst = conn.prepareCall("{call " + name + "(" + Id + ")}");
//            return clst.executeQuery();
//        }
//        return null;
//    }
    
    public ResultSet executeQuery(String name, Vector from, Vector To) throws SQLException {
        if (conn != null) {
            CallableStatement clst = conn.prepareCall("{call " + name + "(" + from + "," + To + ")}");
            return clst.executeQuery();
        } else {
            return null;
        }
    }

    public Vector<EntReportExhibitorFinancialList> getExhibitorIdAndAccountBank(Vector v1, Vector v2) {
        Vector v = new Vector();
        try {
            rs = this.executeQuery("ReportExhibitorFinancialList", v1, v2);
            while (rs.next()) {
                int ExhibitorId = rs.getInt(1);
                String AccountBank = rs.getString(2);
                EntReportExhibitorFinancialList re = new EntReportExhibitorFinancialList(ExhibitorId, AccountBank);
                v.add(re);
            }
        } catch (Exception e) {
        }
        return v;
    }

    public Vector<EntReportExhibitorFinancialListFull> getExhibitorFinancialList(Vector v1, Vector v2) {
//        Vector<ReportAttendance1> v0 = new Vector<ReportAttendance1>();
//      Create a new object of vector<EntReportExhibitorFinancialList1>
        Vector v0 = new Vector();
//      Create a new object of vector<EntReportExhibitorFinancialList>
        Vector<EntReportExhibitorFinancialList> v = new Vector<EntReportExhibitorFinancialList>();
//        assign the vector v equals the vector's values return by getExhibitorIdAndIndentityCardNumber(Vector v1, Vector v2)
        v = getExhibitorIdAndAccountBank(v1, v2);

        try {
            rs = this.executeQuery("ReportExhibitorFinancialList", v1, v2);
            while (rs.next()) {
                Vector temp = new Vector();
//                Get ExhibitorId from the object of vector<EntReportExhibitorFinancialList1>
                int ExhibitorId = rs.getInt(1);
//                if ExhibitorId equals the ExhibitorId get from the object of vector<EntReportExhibitorFinancialList>
                for (EntReportExhibitorFinancialList rEFL : v) {
                    if (rEFL.getExhibitorId() == ExhibitorId) {
                        ExhibitorId = rEFL.getExhibitorId();
                    }
                }
//                Get values
                temp.add(ExhibitorId);
                String FirstName = rs.getString(2);
                temp.add(FirstName);
                String LastName = rs.getString(3);
                temp.add(LastName);
                String Address = rs.getString(4);
                temp.add(Address);
                String AccountName = rs.getString(5);
                for (EntReportExhibitorFinancialList rEFLICN : v) {
                    if (rEFLICN.getExhibitorId() == ExhibitorId) {
                        AccountName = rEFLICN.getAccountBank();
                    }
                }
                temp.add(AccountName);
                String BankName = rs.getString(6);
                temp.add(BankName);
                Date RegisterDate = rs.getDate(7);
                temp.add(RegisterDate);
//                Add Vector v0's values
                v0.add(temp);
            }
        } catch (Exception e) {
            System.out.println("can connect to exhibitor");
            e.printStackTrace();
        }
//        Vector v4 = new Vector();
//        v4 = getExhibitorIdAndAccountBank(v1, v2);
//        for (int i = 0; i < v4.size(); i++) {
//            if (v4.get(i).equals(v0.get(i))) {
//                v0.remove(i);
//            }
//        }
//        Vector v0 = new Vector();
//        try {
//            Vector temp = new Vector();
//            temp.add(1);
//            temp.add("2");
//            temp.add("3");
//            temp.add("4");
//            temp.add("5");
//            temp.add("6");
//            temp.add("01/02/2011");
//            v0.add(temp);
//
//            temp.add(2);
//            temp.add("7");
//            temp.add("8");
//            temp.add("9");
//            temp.add("10");
//            temp.add("11");
//            temp.add("11/02/2011");
//            v0.add(temp);
//        } catch (Exception e) {
//            System.out.println("can connect to exhibitor");
//            e.printStackTrace();
//        }
        return v0;
    }

    //    Write HTML file
    public void writeHTML(Vector v0) throws IOException {
        try {

            BufferedWriter bw = new BufferedWriter(new FileWriter("ReportExhibitorFinancialList.html"));
            bw.write("<html>");
            bw.newLine();
            bw.write("<head>");
            bw.newLine();
            bw.write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\" />");
            bw.newLine();
            bw.write("<title>Exhibitor Financial List</title>");
            bw.newLine();
            bw.write("<style type=\"text / css\">");
            bw.newLine();
            bw.write("<!--");
            bw.newLine();
            bw.write(".style1 {");
            bw.newLine();
            bw.write("font-size: 30px;");
            bw.newLine();
            bw.write("font-weight: bold;");
            bw.newLine();
            bw.write("}");
            bw.newLine();
            bw.write(".style2 {");
            bw.newLine();
            bw.write("font-size: 24px;");
            bw.newLine();
            bw.write("font-weight: bold;");
            bw.newLine();
            bw.write("}");
            bw.newLine();
            bw.write("-->");
            bw.newLine();
            bw.write("</style>");
            bw.newLine();
            bw.write("</head>");

            bw.newLine();
            bw.write("<body>");
            bw.newLine();
            bw.write("<p>&nbsp;</p>");
            bw.newLine();
            bw.write("<p>&nbsp;</p>");
            bw.newLine();
            bw.write("<p>&nbsp;</p>");
            bw.newLine();
            bw.write("<p>&nbsp;</p>");
            bw.newLine();
            bw.write("<table width=\"1043\" height=\"706\" border=\"0\" bordercolor=\"#FFFFFF\">");
            bw.newLine();
            bw.write("<tr>");
            bw.newLine();
            bw.write("<td><table width=\"1000\" height=\"62\" border=\"0\" bordercolor=\"#FFFFFF\">");
            bw.newLine();
            bw.write("<tr>");
            bw.newLine();
            bw.write("<td width=\"199\"><p class=><strong>Expo Management System</strong></p>");
            bw.newLine();
            bw.write("<p class=\"style2\">&nbsp;</p>");
            bw.newLine();
            bw.write("<p>&nbsp;</p></td>");
            bw.newLine();
            bw.write("<td width=\"28\">&nbsp;</td>");
            bw.newLine();
            bw.write("<td width=\"691\"><span class=><h2><strong>EXHIBITOR FINANCIAL LIST</strong></h2></span></td>");
            bw.newLine();
            bw.write("</tr>");
            bw.newLine();
            bw.write("</table></td>");
            bw.newLine();
            bw.write("</tr>");
            bw.newLine();
            bw.write("<tr>");
            bw.newLine();
            bw.write("<td height=\"419\"><table width=\"1011\" border=\"1\">");
            bw.newLine();

            bw.write("<tr>");
            bw.newLine();
            bw.write("<td width=\"146\"><div align=\"center\"><strong>ExhibitorID</strong></div></td>");
            bw.newLine();
            bw.write("<td width=\"135\"><div align=\"center\"><strong>FirstName</strong></div></td>");
            bw.newLine();
            bw.write("<td width=\"133\"><div align=\"center\"><strong>LastName</strong></div></td>");
            bw.newLine();
            bw.write("<td width=\"111\"><div align=\"center\"><strong>Address</strong></div></td>");
            bw.newLine();
            bw.write("<td width=\"169\"><div align=\"center\"><strong>AccountBank</strong></div></td>");
            bw.newLine();
            bw.write("<td width=\"163\"><div align=\"center\"><strong>BankName</strong></div></td>");
            bw.newLine();
            bw.write("<td width=\"163\"><div align=\"center\"><strong>RegisterDate</strong></div></td>");
            bw.newLine();
            bw.write("</tr>");
            bw.newLine();



            for (int i = 0; i < v0.size(); i++) {
                System.out.println(i + " " + (Vector) v0.get(i));
                Vector m = (Vector) v0.get(i);

                bw.write("<tr>");
                bw.newLine();
                bw.write("<td>&nbsp;");
                bw.write(m.get(0).toString());
                bw.write("</td>");
                bw.newLine();
                bw.write("<td>&nbsp;");
                bw.write(m.get(1).toString());
                bw.write("</td>");
                bw.newLine();
                bw.write("<td>&nbsp;");
                bw.write(m.get(2).toString());
                bw.write("</td>");
                bw.newLine();
                bw.write("<td>&nbsp;");
                bw.write(m.get(3).toString());
                bw.write("</td>");
                bw.newLine();
                bw.write("<td>&nbsp;");
                bw.write(m.get(4).toString());
                bw.write("</td>");
                bw.newLine();
                bw.write("<td>&nbsp;");
                bw.write(m.get(5).toString());
                bw.write("</td>");
                bw.newLine();
                bw.write("<td>&nbsp;");
                bw.write(m.get(6).toString());
                bw.write("</td>");
                bw.newLine();
                bw.write("</tr>");
                bw.newLine();



            }


            bw.write("</table></td>");
            bw.newLine();
            bw.write("</tr>");
            bw.newLine();
            bw.write("<tr>");
            bw.newLine();
            bw.write("<td height=\"147\"><table width=\"1003\" height=\"114\" border=\"0\" bordercolor=\"#FFFFFF\">");
            bw.newLine();
            bw.write("<tr>");
            bw.newLine();
            bw.write("<td width=\"462\" height=\"100\"><p><strong>Authozied</strong></p>");
            bw.newLine();
            bw.write("<p><strong>By</strong></p></td>");
            bw.newLine();
            bw.write("<td width=\"483\"><p><strong>Received</strong></p>");
            bw.newLine();
            bw.write("<p><strong>By</strong></p></td>");
            bw.newLine();
            bw.write("</tr>");
            bw.newLine();
            bw.write("</table></td>");
            bw.newLine();
            bw.write("</tr>");
            bw.newLine();
            bw.write("</table>");
            bw.newLine();
            bw.write("<p>&nbsp;</p>");
            bw.newLine();
            bw.write("<p>&nbsp;</p>");
            bw.newLine();
            bw.write("<p>&nbsp;</p>");
            bw.newLine();
            bw.write("</body>");

            bw.newLine();
            bw.write("</html>");

            bw.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {

//        Create a new object of GetDataReportExhibitorFinancialList class
        BssGetDataReportExhibitorFinancialList get = new BssGetDataReportExhibitorFinancialList();
//        Create a new obect of Vector class
        Vector v0 = new Vector();


        Vector v1 = new Vector();
        Vector v2 = new Vector();
//        initial two values
        v1.add("12/27/2010");
        v2.add("12/12/2011");
//        assign v0 equal value which return of GetExhibitorFinancialList(v1, v2)
        v0 = get.getExhibitorFinancialList(v1, v2);

        Vector test = new Vector();

//        Remove value of v0
        for (int i = 0; i < v0.size(); i++) {
            test = (Vector) v0.get(i);
            if (test.get(i).equals(v0.get(i))) {
                v0.remove(i);
            }
        }
        System.out.println(v0);

    }
}

